# coding: utf-8
__author__ = 'yangq'
from tornado import gen
from appcommon.appinstance import mysql_app_pool
from common import timetool


@gen.coroutine
def get_tiku_list(sort, rows, page, order, car_type, exam_type, vip, keyword):
    sql = u"select * from tiku where 1=1 {sql_param} and is_using=1 order by {sort} {order};"
    sql_param = ''
    # if car_type:
    #     sql_param += u"and car_type = '{}' ".format(car_type)
    if exam_type:
        sql_param += u"and exam_type = '{}' ".format(exam_type)
    if vip:
        sql_param += u"and vip = '{}' ".format(vip)
    if keyword:
        sql_param += u"and sub_title like '%{}%' ".format(keyword)
    res_all = yield mysql_app_pool.get_all(sql.format(
        sort=sort,
        order=order,
        sql_param=sql_param
    ), cursor='dict')
    start = (int(page)-1)*int(rows)
    res = (res_all[start:start+int(rows)], len(res_all))
    raise gen.Return(res)


@gen.coroutine
def del_tiku(sub_id):
    current_time = timetool.get_timestamp()
    sql = "update tiku set is_using=0, pubtime={} where sub_id = {}"
    res = yield mysql_app_pool.update(sql.format(current_time, sub_id))
    raise gen.Return(res)


@gen.coroutine
def get_vip_sort_id(vip):
    sql = "select max(vip_id) from tiku where vip = '{}'"
    info = yield mysql_app_pool.get_one(sql.format(vip))
    res = int(info[0])+1 if info else ""
    raise gen.Return(res)


@gen.coroutine
def get_last_order_id():
    sql = "select max(order_id) from tiku "
    info = yield mysql_app_pool.get_one(sql)
    res = int(info[0])+1 if info else ""
    raise gen.Return(res)


@gen.coroutine
def update_tiku(args):
    args['pubtime'] = timetool.get_timestamp()
    if 'VIP' in args['vip']:
        args['vip_id'] = yield get_vip_sort_id(args['vip'])
    args['order_id'] = yield get_last_order_id()
    print type(args['sub_infos'])
    if args['id']:
        sql = "update tiku set car_type='{car_type}',exam_type='{exam_type}', sub_chapte='{sub_chapte}',vip='{vip}'," \
              "sub_title='{sub_title}',sub_pic='{sub_pic}',a='{a}',b='{b}',c='{c}',d='{d}',answer='{answer}',jqyy='{jqyy}'," \
              "sub_infos='{sub_infos}',info_pic='{info_pic}',vip_pic='{vip_pic}', sub_class='{sub_class}',sub_type='{sub_type}',dtjq='{dtjq}',pubtime={pubtime}, sub_id='{sub_id}', city='{city}' where id={id};"
        yield mysql_app_pool.update(sql.format(**args))
    else:
        sql = "insert into tiku (sub_id,car_type,exam_type,sub_chapte,vip," \
              "sub_title,sub_pic,a,b,c,d,answer,jqyy,sub_infos,info_pic,vip_pic,sub_class,sub_type,dtjq,pubtime, city, order_id) values ('{sub_id}','{car_type}','{exam_type}', '{sub_chapte}','{vip}'," \
              "'{sub_title}','{sub_pic}','{a}','{b}','{c}','{d}','{answer}','{jqyy}','{sub_infos}'," \
              "'{info_pic}','{vip_pic}', '{sub_class}','{sub_type}','{dtjq}',{pubtime}, '{city}', {order_id});"
        yield mysql_app_pool.insert_one(sql.format(**args))
    res = 0
    raise gen.Return(res)


@gen.coroutine
def get_vip_info_with_sub_id(sub_id):
    sql = "select sub_id, vip, vip_id from tiku where sub_id = {}"
    res = yield mysql_app_pool.get_one(sql.format(sub_id), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def update_vip_id(sub_id, vip_id, exam_type):
    current_time = timetool.get_timestamp()
    sql = "update tiku set vip_id={}, pubtime={} where sub_id = {} and exam_type={}"
    res = yield mysql_app_pool.update(sql.format(vip_id, current_time, sub_id, exam_type))
    raise gen.Return(res)


@gen.coroutine
def update_vip_id_2_big(old_vip_id, vip_id, vip, exam_type):
    current_time = timetool.get_timestamp()
    sql = "update tiku set vip_id=vip_id-1, pubtime={} where vip = '{}' and vip_id >{} and vip_id <= {} and exam_type = {}"
    res = yield mysql_app_pool.update(sql.format(current_time, vip, old_vip_id, vip_id, exam_type))
    raise gen.Return(res)


@gen.coroutine
def update_vip_id_2_small(old_vip_id, vip_id, vip, exam_type):
    current_time = timetool.get_timestamp()
    sql = "update tiku set vip_id=vip_id+1, pubtime={} where vip = '{}' and vip_id <{} and vip_id >= {} and exam_type={}"
    res = yield mysql_app_pool.update(sql.format(current_time, vip, old_vip_id, vip_id, exam_type))
    raise gen.Return(res)


@gen.coroutine
def get_media_list(type, file_name):
    param = u"and file_name like '%{}%' ".format(file_name) if file_name else ""
    sql = (
        u"select * from tiku_media where type={} {} order by pubtime desc"
    )
    info = yield mysql_app_pool.get_all(sql.format(
        type, param
    ), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def add_media_2_mysql(file_name, type):
    current_time = timetool.get_timestamp()
    sql = (
        "insert into tiku_media (file_name, pubtime, type) "
        u"values ('{}', {}, '{}')"
    )
    yield mysql_app_pool.insert_one(sql.format(
        file_name, current_time, type
    ))
    raise gen.Return(1)


@gen.coroutine
def delete_media_from_mysql(type):
    sql = (
        u"delete from tiku_media where type = {}"
    )
    yield mysql_app_pool.delete(sql.format(
        type
    ))
    raise gen.Return(1)


@gen.coroutine
def del_media_with_id(id):
    sql = (
        u"delete from tiku_media where id = {}"
    )
    yield mysql_app_pool.delete(sql.format(
        id
    ))
    raise gen.Return(1)


@gen.coroutine
def get_media_with_id(id):
    sql = (
        u"select file_name, type from tiku_media where id = {}"
    )
    res = yield mysql_app_pool.get_one(sql.format(id), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_video_list(file_name):
    param = u"and file_name like '%{}%' ".format(file_name) if file_name else ""
    sql = (
        u"select * from tiku_video where 1=1 {} order by pubtime desc"
    )
    info = yield mysql_app_pool.get_all(sql.format(
        param
    ), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_video_with_id(id):
    sql = (
        u"select file_name, pic from tiku_video where id = {}"
    )
    res = yield mysql_app_pool.get_one(sql.format(id), cursor='dict')
    if res:
        res['file_name'] = res['file_name'] if res['file_name'] else 'nothing'
        res['pic'] = res['pic'] if res['pic'] else 'nothing'
    raise gen.Return(res)


@gen.coroutine
def del_video_with_id(id):
    sql = (
        u"delete from tiku_video where id = {}"
    )
    yield mysql_app_pool.delete(sql.format(
        id
    ))
    raise gen.Return(1)


@gen.coroutine
def add_video_2_mysql(file_name, pic, title, content, type, car_type):
    current_time = timetool.get_timestamp()
    sql = (
        "insert into tiku_video (file_name, pic, title, content, type, car_type, view_cnt, pubtime) "
        u"values ('{}', '{}', '{}','{}', {}, {}, 0, {})"
    )
    yield mysql_app_pool.insert_one(sql.format(
        file_name, pic, title, content, type, car_type, current_time
    ))
    raise gen.Return(1)


@gen.coroutine
def update_video_info(id, title, content, type, car_type, pic_name):
    sql = u"update tiku_video set title='{}', content='{}', type={}, car_type={}, pic='{}' where id = {}"
    yield mysql_app_pool.insert_one(sql.format(
        title, content, type, car_type, pic_name, id
    ))
    raise gen.Return(1)


@gen.coroutine
def get_sub_info_with_sub_id(sub_id):
    sql = "select * from tiku where sub_id = {}"
    res = yield mysql_app_pool.get_one(sql.format(sub_id), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def update_sort_id(sub_id, order_id):
    current_time = timetool.get_timestamp()
    sql = "update tiku set order_id={}, pubtime={} where sub_id = {}"
    res = yield mysql_app_pool.update(sql.format(order_id, current_time, sub_id))
    raise gen.Return(res)


@gen.coroutine
def update_sort_id_2_big(old_order_id, order_id):
    current_time = timetool.get_timestamp()
    sql = "update tiku set order_id=order_id-1, pubtime={} where  order_id >{} and order_id <= {}"
    res = yield mysql_app_pool.update(sql.format(current_time, old_order_id, order_id))
    raise gen.Return(res)


@gen.coroutine
def update_sort_id_2_small(old_order_id, order_id):
    current_time = timetool.get_timestamp()
    sql = "update tiku set order_id=order_id+1, pubtime={} where order_id <{} and order_id >= {} "
    res = yield mysql_app_pool.update(sql.format(current_time, old_order_id, order_id))
    raise gen.Return(res)


@gen.coroutine
def get_all_tiku():
    sql = (
        "select a.*, IFNULL(b.err_cnt,0) as err_cnt, IFNULL(b.total_cnt,0) as total_cnt from tiku a LEFT JOIN tiku_err_cnt b "
        "on a.sub_id = b.sub_id where a.is_using = 1 order by a.sub_id "
    )
    err_cnt_list = yield mysql_app_pool.get_all(sql, cursor='dict')
    list_len = len(err_cnt_list)
    for index, item in enumerate(err_cnt_list):
        if index >= 0 and index < list_len * 1 / 15:
            item['stars'] = 5
        elif index >= list_len * 1 / 15 and index < list_len * 3 / 15:
            item['stars'] = 4
        elif index >= list_len * 3 / 15 and index < list_len * 6 / 15:
            item['stars'] = 3
        elif index >= list_len * 6 / 15 and index < list_len * 10 / 15:
            item['stars'] = 2
        elif index >= list_len * 10 / 15 and index < list_len * 15 / 15:
            item['stars'] = 1
        else:
            item['stars'] = 0
    raise gen.Return(err_cnt_list)